﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using IHISCETeim.Library;

namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeReceivablesExportFanHuanLiXi : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部返还利息2.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "模板";
            }
        }

        #region 属性 (查询条件)

        protected string ZDYongHu
        {
            get
            {
                return GetRequestValue<string>("ZDYongHu", "");

            }
        }

        protected string JingXiaoShang
        {
            get
            {
                return GetRequestValue<string>("JingXiaoShang", "");

            }
        }
        protected string HeTongHao
        {
            get
            {
                return GetRequestValue<string>("HeTongHao", "");

            }
        }

        protected string JiHao
        {
            get
            {
                return GetRequestValue<string>("JiHao", "");

            }
        }

        protected string JiQiState
        {
            get
            {
                return GetRequestValue<string>("JiQiState", "-");
            }
        }
        protected string XiaoShouLeiXing
        {
            get
            {
                return GetRequestValue<string>("XiaoShouLeiXing", "");
            }

        }
        protected string YingYeJingLi
        {
            get
            {
                return GetRequestValue<string>("YingYeJingLi", "");
            }

        }
        protected string XiaoShouShiJianFrom
        {
            get
            {
                return GetRequestValue<string>("XiaoShouShiJianFrom", "");

            }
        }
        protected string XiaoShouShiJianTo
        {
            get
            {
                if (string.IsNullOrEmpty(XiaoShouShiJianFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("XiaoShouShiJianTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(XiaoShouShiJianFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }
        protected string FanHuanLiXiState
        {
            get
            {
                return GetRequestValue<string>("FanHuanLiXiState", "");

            }
        }
        protected string FanHuanDateFrom
        {
            get
            {
                return GetRequestValue<string>("FanHuanDateFrom", "");

            }
        }
        protected string FanHuanDateTo
        {
            get
            {
                if (string.IsNullOrEmpty(FanHuanDateFrom))
                {
                    return "";
                }
                else
                {
                    string todate = GetRequestValue<string>("FanHuanDateTo", "");
                    if (string.IsNullOrEmpty(todate))
                        return Convert.ToDateTime(FanHuanDateFrom).AddDays(1).ToShortDateString();
                    else
                        return todate;
                }

            }
        }

        protected string CreateDateFrom
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本周周一
                DateTime endWeek = startWeek.AddDays(6); //本周周日
                return startWeek.ToString("yyyy-MM-dd");
            }
        }
        protected string CreateDateTo
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                DateTime startWeek = dt.AddDays(1 - Convert.ToInt32(dt.DayOfWeek.ToString("d"))); //本周周一
                DateTime endWeek = startWeek.AddDays(6); //本周周日
                return endWeek.ToString("yyyy-MM-dd");

            }
        }


        #endregion
        protected string GetSQLWhere()
        {
            //营业经理权限 2014-02-21
            //string agentList = base.FTradeAgentPowerSqlWhere();
            string wheresql = " 1=1 " ;

            string sqlcommonFiled = "";
            if (!string.IsNullOrEmpty(JingXiaoShang))
            {
                sqlcommonFiled = QueryOperatorsEnumHelper.ConvertCommonOperator("JingXiaoShang", QueryOperatorsEnum.等于, JingXiaoShang);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + sqlcommonFiled;
                }
                else
                {
                    wheresql = sqlcommonFiled;
                }
            }

            if (!string.IsNullOrEmpty(ZDYongHu))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("ZDYongHu", QueryOperatorsEnum.包含, ZDYongHu);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(HeTongHao))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("HeTongHao", QueryOperatorsEnum.等于, HeTongHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            if (!string.IsNullOrEmpty(YingYeJingLi))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("YingYeJingLi", QueryOperatorsEnum.等于, YingYeJingLi);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            if (!string.IsNullOrEmpty(XiaoShouShiJianFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("XiaoShouShiJian", QueryOperatorsEnum.范围, XiaoShouShiJianFrom + "|" + XiaoShouShiJianTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            string dutySQL = "";
            if (!string.IsNullOrEmpty(JiHao))
            {
                dutySQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiHao", QueryOperatorsEnum.包含, JiHao);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + dutySQL;
                }
                else
                {
                    wheresql = dutySQL;
                }
            }

            if (JiQiState != "-")
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("JiQiState", QueryOperatorsEnum.等于, JiQiState);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }
            if (XiaoShouLeiXing != "")
            {
                string[] xslxs = XiaoShouLeiXing.Split(new char[] { ',' });
                string xslxsql = " XiaoShouLeiXing in (''";
                foreach (string itm in xslxs)
                {
                    if (!string.IsNullOrEmpty(itm))
                    {
                        xslxsql += string.Format(",'{0}'", itm);
                    }
                }
                xslxsql += ")";

                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + xslxsql;
                }
                else
                {
                    wheresql = xslxsql;
                }
            }

            //
            if (FanHuanLiXiState != "")
            {
                string[] xslxs = FanHuanLiXiState.Split(new char[] { ',' });
                string xslxsql = "";// " FanHuanLiXiState in (";
                foreach (string itm in xslxs)
                {
                    if (!string.IsNullOrEmpty(itm))
                    {
                        xslxsql += string.Format(",'{0}'", itm);
                    }
                }
                //  xslxsql += ")";

                xslxsql = xslxsql.Substring(1, xslxsql.Length - 1);
                xslxsql = " FanHuanLiXiState in (" + xslxsql + ")";


                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + xslxsql;
                }
                else
                {
                    wheresql = xslxsql;
                }
            }

            if (!string.IsNullOrEmpty(FanHuanDateFrom))
            {
                string DOWNSQL = QueryOperatorsEnumHelper.ConvertCommonOperator("FanHuanDate", QueryOperatorsEnum.范围, FanHuanDateFrom + "|" + FanHuanDateTo);
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql += " and " + DOWNSQL;
                }
                else
                {
                    wheresql = DOWNSQL;
                }
            }

            return wheresql;
        }

        protected DataTable sumdt = new DataTable();
        protected void Page_Load(object sender, EventArgs e)
        {

            DataTableCollection dts = FTradeReportController.GetFanHuanLiXi(GetSQLWhere());

            DataTable allAgentlist = dts[0];
            DataTable exportDT = dts[1];
            DataTable sboagentlist = SBOController.GetAgentList();//所有经销商列表

            if (allAgentlist.Rows.Count <= 0)
            {
                Response.Write("没有任何数据...");
                return;
            }

            InitializeWorkbook();

            string AliasName = "";
            int sheetindex = 1;//从第二个工作表开始
            foreach (DataRow dr in allAgentlist.Rows)
            {
                AliasName = dr["JingXiaoShang"].ToString();
                DataTable detaildt = new DataTable();
                if (AliasName == "湖南金力源")
                {
                    detaildt = exportDT.Select("JingXiaoShang in('" + AliasName + "','湖南金土地')").CopyToDataTable();
                }
                else
                {
                    detaildt = exportDT.Select("JingXiaoShang='" + AliasName + "'").CopyToDataTable();
                }

                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, AliasName);
                //经销商全称                
                DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'", AliasName.Trim()));
                string cardname = drs[0]["CardName"].ToString();

                if (detaildt.Rows.Count <= 0)
                    continue;
                WriteSheet(sheet2, detaildt, cardname);

                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }
            //隐藏
            hssfworkbook.RemoveSheetAt(0);
            //
            WriteToFile();
        }


        private void WriteSheet(ISheet sheet2, DataTable exportDT, string cardname)
        {
            sheet2.GetRow(5).GetCell(1).SetCellValue(cardname);
            //.Select(" FanHuanLiXi>0 and FanHuanLiXiState IN('正常(未结算)')")
            //exportDT.Select("FanHuanLiXi>0 and FanHuanLiXiState IN('正常(未结算)')").CopyToDataTable().Compute("SUM(FanHuanLiXi)").ToString("N4");
            string KFHJinE = exportDT.Select("FanHuanLiXi>0").CopyToDataTable().Compute("SUM(FanHuanLiXi)", " FanHuanLiXiState ='正常(未结算)'").ToString();//; Convert.ToDecimal(exportDT.Compute("SUM(FanHuanLiXi)", "FanHuanLiXi>0 ")).ToString("N4");
            KFHJinE = KFHJinE == "" ? "0" : KFHJinE;
            sheet2.GetRow(9).GetCell(0).SetCellValue(string.Format("    截止{0},贵公司整机应收账款分期结清明细如下,贵司可享受的返还利息金额为：{1}元.", Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-01")).AddDays(-1).ToString("yyyy年MM月dd日"), KFHJinE));
            //
            sheet2.GetRow(19).GetCell(0).SetCellValue(string.Format("     以上数据请贵司核对无误后签字盖章,并于{0}前传真(传真:0592-6292200)至本公司,如有疑议，请致电我司财务部王晓娟（TEL：0592-6292250）。谢谢合作！", DateTime.Now.AddMonths(1).ToString("yyyy年MM月05日")));
            sheet2.GetRow(22).GetCell(6).SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"));

            //开始FOR各信息
            if (exportDT.Rows.Count > 6)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(11);
                MyInsertRow((HSSFSheet)sheet2, 12, exportDT.Rows.Count -6, 源格式行);
            }

            int rowindexBase =11;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;            

            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
                //合并处理
                //  sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 4, 5));
                // row1.GetCell(4).CellStyle = style12;

                row1.GetCell(0).SetCellValue(dr["HeTongHao"].ToString());
                row1.GetCell(1).SetCellValue(dr["XingHao"].ToString());
                row1.GetCell(2).SetCellValue(dr["JiHao"].ToString());
                row1.GetCell(3).SetCellValue(Convert.ToDouble(dr["HeTongJinE"]));
                //row1.CreateCell(4).SetCellValue(Convert.ToDouble(dr["YiShouKuanXiang"]));
                //row1.CreateCell(5).SetCellValue(Convert.ToDouble(dr["WeiShouKuanXiang"]));
                //row1.CreateCell(6).SetCellValue(Convert.ToDouble(dr["YuQiKuanXiang"]));
                row1.GetCell(4).SetCellValue(Convert.ToDouble(dr["FanHuanLiXi"]));
                row1.GetCell(5).SetCellValue(dr["FanHuanLiXiState"].ToString() == "正常(未结算)" ? Convert.ToDouble(dr["FanHuanLiXi"]) : 0);
                row1.GetCell(6).SetCellValue(dr["FanHuanLiXiState"].ToString().Replace("空",""));
                row1.GetCell(7).SetCellValue(dr["FuKuanTiaoKuan"].ToString());
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

        }



        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("返还利息清单{0}.xls", DateTime.Now.ToString("yyyy-MM-dd"));
        }
    }
}